// .........................................................................
// Title: price_characteristics_panels.do
//
// Build consolidated security-level panel merging bond prices, ownership
// information, and security characteristics
// .........................................................................

* --------------------------------------
* Panel with bond characteristics
* --------------------------------------

* load trace
use "$tmp/trace_clean_prices_w", clear
gen year = year(dofw(date_w))
gen date_m = mofd(dofw(date_w))
format %tm date_m

* add in yields
mmerge cusip date_w using "$tmp/trace_clean_yields_w", unmatched(m)

* add duration and other bond characteristics
mmerge cusip using "$tmp/ciq_static_characteristics_processed", unmatched(m)
mmerge cusip using "$tmp/consolidated_static_dummies", unmatched(m) ukeep(bc_is_floating bc_is_senior bc_is_callable) update
mmerge cusip date_m using "$tmp/duration_merged", unmatched(m)
drop _merge
cap drop R_* N_*
gen_duration_category

* add parent aggregation
gen issuer_number = substr(cusip, 1, 6)
mmerge issuer_number using "$raw/cmns/cmns_aggregation", unmatched(m)

* merging in associated issuers file
mmerge issuer_number using "$output/cgs/cgs_ai_aggregation", unmatched(m) umatch(cusip6) ukeep(ai_cusip6)
replace ai_cusip6 = issuer_number if missing(ai_cusip6)
drop _merge *source*

* add mergent issuer ids
mmerge cusip using "$tmp/mergent_issuer_ids.dta", unmatched(m)

* merge with treasury yields
mmerge duration_m date_w using "$tmp/crsp_treasury_yields_w", umatch(duration date_w) unmatched(m)
rename yield_tm treasury_yield

* add quarters
gen date_q = qofd(dofw(date_w))
format %tq date_q
gsort cusip date_q date_w

* add merged moodys-sp ratings
cap drop rating_cat
cap drop rating_cat_pm
mmerge cusip year using "$tmp/issue_ratings_merged_sp_moodys_y", unmatched(m) uname(spm_)
rename spm_rating_cat rating_cat
rename spm_rating_cat_pm rating_cat_pm

* additional issuance dates from sec master (if missing)
mmerge cusip using "$tmp/secmaster_issuance_dates", unmatched(m) uname(sm_)
cap rename iq_offer_date issuance_date
replace issuance_date = sm_issuance_date if missing(issuance_date)
drop sm_*

* merge in holdings data
gen _date_q = date_q - 1
mmerge cusip _date_q using "$tmp/holdings_mf_ins_issuance_trace_universe_refined", unmatched(m) umatch(cusip date_q)
rename _merge holdings_merge_flag


* consolidated issuer ids
gen consolidated_issuer_id = mergent_issuer_id
tostring consolidated_issuer_id, replace
replace consolidated_issuer_id = ai_cusip6 if missing(consolidated_issuer_id)
assert ~missing(consolidated_issuer_id)

* save
cap drop _merge
save "$tmp/trace_with_characteristics_holdings_complete", replace

* monthly frequency
gsort cusip date_m date_w
by cusip date_m: keep if _n == _N
save "$tmp/trace_with_characteristics_holdings_complete_m", replace

* --------------------------------------
* Yields to maturity
* --------------------------------------

* computing yields for monthly data: prepare data
use "$tmp/trace_with_characteristics_holdings_complete_m", clear
mmerge cusip using "$tmp/interest_details_consolidated.dta", unmatched(m)
gen maturity_m = mofd(maturity_date)
format %tm maturity_m
cap drop residual_maturity_y
gen residual_maturity_y = round((maturity_m - date_m) / 12)
drop if missing(residual_maturity_y) | missing(coupon)
cap drop coupon_pct
drop if residual_maturity_y < 0
gen coupon_pct = coupon / 100
keep cusip date_m trace_median_price_w coupon_pct coupon duration_y residual_maturity_y
save "$tmp/_trace_eq_yields_forpy_m_xt", replace

* run python method to obtain yields to maturity
shell $python_interpreter_path $code_path/scripts/compute_ytm.py ///
    --input_file "$tmp/_trace_eq_yields_forpy_m_xt.dta" ///
    --coupon_field "coupon" ///
    --years_to_maturity_field "residual_maturity_y" ///
    --price_field "trace_median_price_w" ///
    --out_file "$tmp/_trace_eq_yields_frompy_m_xt.dta"

* retrieve ytm from python
use "$tmp/_trace_eq_yields_frompy_m_xt", clear
cap drop index
replace date_m = mofd(dofc(date_m))
format %tm date_m
rename py_ytm bond_ytm
keep cusip date_m bond_ytm
save "$tmp/_bond_ytm_m_xt", replace

* add yield spreads to trace dataframe
use "$tmp/trace_with_characteristics_holdings_complete_m", clear
mmerge cusip date_m using "$tmp/_bond_ytm_m_xt", unmatched(m)
replace treasury_yield = treasury_yield * 100
replace bond_ytm = bond_ytm * 100
gen bond_yield_spread = bond_ytm - treasury_yield
gen trace_yield_spread = trace_median_yield - treasury_yield
winsor2 trace_median_yield treasury_yield bond_ytm bond_yield_spread trace_yield_spread

* add returns to panel
mmerge cusip date_m using "$tmp/trace_enhanced_clean_returns_hedged_m", unmatched(m)
mmerge date_m using "$tmp/gz_m", unmatched(m)
drop _merge
gen hedged_return_pp = hedged_return * 100
gen net_return_pp = net_return * 100
save "$tmp/trace_merged_panel_complete_m", replace
